package org.jeecgframework.web.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mybatis.ext.WiseCoreService;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.*;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.web.service.CbsInspectionEvaluateServiceI;
import org.jeecgframework.web.service.ZyCommonService;
import org.jeecgframework.web.service.ZyInspectionPlanServiceI;
import org.jeecgframework.web.system.pojo.base.*;
import org.jeecgframework.web.system.service.SystemService;
import org.jeecgframework.web.util.StaticValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


@Service("cbsInspectionEvaluateService")
@Transactional
public class CbsInspectionEvaluateServiceImpl extends CommonServiceImpl implements CbsInspectionEvaluateServiceI {

    @Autowired
    private ZyCommonService zyCommonService;
    @Autowired
    private SystemService systemService;
    @Autowired
    private WiseCoreService wiseCoreService;

    @Autowired
    private ZyInspectionPlanServiceI zyInspectionPlanService;

    public <T> void delete(T entity) {
        super.delete(entity);
        //执行删除操作配置的sql增强
        this.doDelSql((CbsInspectionEvaluateEntity) entity);
    }

    public <T> Serializable save(T entity) {
        Serializable t = super.save(entity);
        //执行新增操作配置的sql增强
        this.doAddSql((CbsInspectionEvaluateEntity) entity);
        return t;
    }

    public <T> void saveOrUpdate(T entity) {
        super.saveOrUpdate(entity);
        //执行更新操作配置的sql增强
        this.doUpdateSql((CbsInspectionEvaluateEntity) entity);
    }

    /**
     * 默认按钮-sql增强-新增操作
     *
     * @return
     */
    public boolean doAddSql(CbsInspectionEvaluateEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-更新操作
     *
     * @return
     */
    public boolean doUpdateSql(CbsInspectionEvaluateEntity t) {
        return true;
    }

    /**
     * 默认按钮-sql增强-删除操作
     *
     * @return
     */
    public boolean doDelSql(CbsInspectionEvaluateEntity t) {
        return true;
    }

    /**
     * 替换sql中的变量
     *
     * @param sql
     * @return
     */
    public String replaceVal(String sql, CbsInspectionEvaluateEntity t) {
        sql = sql.replace("#{id}", String.valueOf(t.getId()));
        sql = sql.replace("#{last_evaluate}", String.valueOf(t.getLastEvaluate()));
        sql = sql.replace("#{service_evaluate}", String.valueOf(t.getServiceEvaluate()));
        sql = sql.replace("#{talk_evaluate}", String.valueOf(t.getTalkEvaluate()));
        sql = sql.replace("#{train_evaluate}", String.valueOf(t.getTrainEvaluate()));
        sql = sql.replace("#{skill_evaluate}", String.valueOf(t.getSkillEvaluate()));
        sql = sql.replace("#{opinion}", String.valueOf(t.getOpinion()));
// 		sql  = sql.replace("#{inspection_id}",String.valueOf(t.getInspectionId()));
        sql = sql.replace("#{create_name}", String.valueOf(t.getCreateName()));
        sql = sql.replace("#{create_by}", String.valueOf(t.getCreateBy()));
        sql = sql.replace("#{create_date}", String.valueOf(t.getCreateDate()));
        sql = sql.replace("#{update_name}", String.valueOf(t.getUpdateName()));
        sql = sql.replace("#{update_by}", String.valueOf(t.getUpdateBy()));
        sql = sql.replace("#{update_date}", String.valueOf(t.getUpdateDate()));
        sql = sql.replace("#{UUID}", UUID.randomUUID().toString());
        return sql;
    }

    @Override
    public List<List<String>> queryIpEvaluateReportDataOfTp(String year, String ipCompany, String egIds) {
        String egIdStr = "";
        if (StringUtil.isNotEmpty(egIds)) {
            egIdStr = "'" + egIds.replace(",", "','") + "'";
        }
        List<List<String>> dataList = new ArrayList<List<String>>();
        StringBuffer sql = new StringBuffer();//已有月份评价平均分
        sql.append(" SELECT ");
        sql.append("	MONTH (ip.end_time) AS ipMonth, ");
        sql.append("	IFNULL(TRUNCATE(AVG(ie.last_evaluate),2),0) AS avgScore, ");
        sql.append("	COUNT(ip.id) ipCount, ");
        sql.append("	COUNT(ie.id) ieCount, ");
        sql.append("	SUM(IF(last_evaluate = 5,1,0)) AS five, ");
        sql.append("	SUM(IF(last_evaluate = 4,1,0)) AS four, ");
        sql.append("	SUM(IF(last_evaluate = 3,1,0)) AS three, ");
        sql.append("	SUM(IF(last_evaluate = 2,1,0)) AS two, ");
        sql.append("	SUM(IF(last_evaluate = 1,1,0)) AS one, ");
        sql.append("	IFNULL(SUM(ie.last_evaluate),0) AS sumLastEvaluate ");
        sql.append(" FROM ");
        sql.append(" 	zy_inspection_plan ip ");
        sql.append(" LEFT JOIN cbs_inspection_evaluate ie ON ip.id = ie.inspection_id ");
        sql.append(" WHERE ");
        sql.append(" 	YEAR (ip.ip_plan_date) = '").append(year).append("' ");
        sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
        sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_1).append("' ");
        if (StringUtil.isNotEmpty(ipCompany)) {
            sql.append(" AND ip.company = '").append(ipCompany).append("' ");
        }
        if (StringUtil.isNotEmpty(egIdStr)) {
            sql.append(" AND ip.engineer IN(").append(egIdStr).append(") ");
        }
        sql.append(" GROUP BY ipMonth ");
        sql.append(" ORDER BY ipMonth ");
        List<Map<String, Object>> dataMapList = super.findForJdbc(sql.toString());
        for (int r = 0; r < 8; r++) {
            List<String> rowList = new ArrayList<String>();
            if (r == 0) {
                rowList.add("评价平均分");
            } else if (r == 1) {
                rowList.add("评价总数");
            } else if (r == 2) {
                rowList.add("任务总数");
            } else if (r == 3) {
                rowList.add("评价5分的数量");
            } else if (r == 4) {
                rowList.add("评价4分的数量");
            } else if (r == 5) {
                rowList.add("评价3分的数量");
            } else if (r == 6) {
                rowList.add("评价2分的数量");
            } else if (r == 7) {
                rowList.add("评价1分的数量");
            }
            double rowTotal = 0;
            boolean had = false;
            double r1_ieCount = 0;//第一行总计用 ： 所有总体评价分数 / 评价数量
            int sumLastEvaluate = 0;//第一行总计用 ： 所有总体评价分数 / 评价数量
            for (int month = 1; month < 13; month++) {
                for (Map<String, Object> map : dataMapList) {
                    if (month == Integer.valueOf(map.get("ipMonth").toString())) {
                        double cell = 0;
                        if (r == 0) {
                            cell = Double.valueOf(map.get("avgScore").toString());
                        } else if (r == 1) {
                            cell = Integer.valueOf(map.get("ipCount").toString());
                        } else if (r == 2) {
                            cell = Integer.valueOf(map.get("ieCount").toString());
                        } else if (r == 3) {
                            cell = Integer.valueOf(map.get("five").toString());
                        } else if (r == 4) {
                            cell = Integer.valueOf(map.get("four").toString());
                        } else if (r == 5) {
                            cell = Integer.valueOf(map.get("three").toString());
                        } else if (r == 6) {
                            cell = Integer.valueOf(map.get("two").toString());
                        } else if (r == 7) {
                            cell = Integer.valueOf(map.get("one").toString());
                        }
                        r1_ieCount += Integer.valueOf(map.get("ieCount").toString());//第一行总计用 ： 所有总体评价分数 / 评价数量
                        sumLastEvaluate += Integer.valueOf(map.get("sumLastEvaluate").toString());//第一行总计用 ： 所有总体评价分数 / 评价数量
                        rowTotal += cell;
                        rowList.add(cell + "");
                        had = true;
                        break;
                    }
                }
                if (!had) {
                    rowList.add("0");
                }
                had = false;
            }
            if (r == 0 && r1_ieCount != 0) {//第一行总计用 ： 所有总体评价分数 / 评价数量
                rowTotal = new BigDecimal(sumLastEvaluate / r1_ieCount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
            }
            rowList.add(rowTotal + "");
            rowTotal = 0;
            dataList.add(rowList);
        }
        return dataList;
    }

    @Override
    public void queryIpEvaluateReportDataOfEg(AjaxJson aj, String year, String ipCompany, String egIds) {
        String egIdStr = "";
        if (StringUtil.isNotEmpty(egIds)) {
            egIdStr = "'" + egIds.replace(",", "','") + "'";
        }
        List<List<Object>> dataList = new ArrayList<List<Object>>();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	eg.id AS egId, ");
        sql.append(" 	eg.`NAME` AS egName, ");
        sql.append(" 	dp.org_code AS orgCode, ");
        sql.append(" 	dp.shortname ");
        sql.append(" FROM ");
        sql.append(" 	zy_engineer eg ");
        sql.append(" LEFT JOIN t_s_base_user bu ON bu.id = eg.ts_base_user_id ");
        sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_depart dp ON dp.id = uo.org_id ");
        sql.append(" WHERE ");
        sql.append(" 	dp.org_code = '").append(ipCompany).append("' ");
        sql.append(" AND dp.logic_delete = 0 ");
        if (StringUtil.isNotEmpty(egIdStr)) {
            sql.append(" AND eg.id IN(").append(egIdStr).append(") ");
        }
        List<Map<String, Object>> egMapList = super.findForJdbc(sql.toString());//查询所有工程师及所属机构

        System.out.println("sql1:"+sql);
        sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	ip.engineer, ");
        sql.append(" 	IFNULL(TRUNCATE(AVG(ie.last_evaluate),2),0) AS avgScore, ");
        sql.append(" 	COUNT(ip.id) ipCount, ");
        sql.append(" 	COUNT(ie.id) ieCount, ");
        sql.append(" 	SUM(IF(last_evaluate = 5,1,0)) AS five, ");
        sql.append(" 	SUM(IF(last_evaluate = 4,1,0)) AS four, ");
        sql.append(" 	SUM(IF(last_evaluate = 3,1,0)) AS three, ");
        sql.append(" 	SUM(IF(last_evaluate = 2,1,0)) AS two, ");
        sql.append(" 	SUM(IF(last_evaluate = 1,1,0)) AS one ");
        sql.append(" FROM ");
        sql.append("  zy_inspection_plan ip  ");
        sql.append(" LEFT JOIN cbs_inspection_evaluate ie ON ip.id = ie.inspection_id ");
        sql.append(" WHERE ");
        sql.append(" 	YEAR (ip.ip_plan_date) = '").append(year).append("' ");
        sql.append("	AND ip.company = '").append(ipCompany).append("' ");
        sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
        sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_1).append("' ");
        if (StringUtil.isNotEmpty(egIdStr)) {
            sql.append(" AND ip.engineer IN(").append(egIdStr).append(") ");
        }
        sql.append(" GROUP BY ip.engineer ");

        System.out.println("sql2:"+sql);
//        sql.append(" ORDER BY avgScore DESC,ipCount DESC ");
        List<Map<String, Object>> egGroupMapList = super.findForJdbc(sql.toString());
        for (Map<String, Object> egMap : egMapList) {
            List<Object> rowList = new ArrayList<Object>();
            rowList.add(egMap);
            boolean had = false;
            for (Map<String, Object> egGroupMap : egGroupMapList) {
                if (egGroupMap.get("engineer").toString().equals(egMap.get("egId").toString())) {
                    rowList.add(null != egGroupMap.get("avgScore") ? egGroupMap.get("avgScore").toString() : "0");
                    rowList.add(null != egGroupMap.get("ipCount") ? egGroupMap.get("ipCount").toString() : "0");
                    rowList.add(null != egGroupMap.get("ieCount") ? egGroupMap.get("ieCount").toString() : "0");
                    rowList.add(null != egGroupMap.get("five") ? egGroupMap.get("five").toString() : "0");
                    rowList.add(null != egGroupMap.get("four") ? egGroupMap.get("four").toString() : "0");
                    rowList.add(null != egGroupMap.get("three") ? egGroupMap.get("three").toString() : "0");
                    rowList.add(null != egGroupMap.get("two") ? egGroupMap.get("two").toString() : "0");
                    rowList.add(null != egGroupMap.get("one") ? egGroupMap.get("one").toString() : "0");
                    had = true;
                    break;
                }
            }
            if (!had) {
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
            }
            dataList.add(rowList);
        }
        Collections.sort(dataList, new Comparator<List<Object>>() {
            public int compare(List<Object> list1, List<Object> list2) {
                //按任务总数 由高到低排序（评价平均分排序放在js中），之所以没在sql加order 因为 结果外层还有个工程师列表循环
                return Double.valueOf(list2.get(2).toString()).compareTo(Double.valueOf(list1.get(2).toString()));
//        		if(Double.valueOf(list1.get(2).toString()) - Double.valueOf(list2.get(2).toString()) < 0){
//        			return 1;
//        		}
//        		return -1;
            }
        });
//		sql = new StringBuffer();
//		sql.append(" SELECT ");
//		sql.append(" 	IFNULL(TRUNCATE(AVG(ie.last_evaluate),2),0) AS avgScore ");
//		sql.append(" FROM ");
//		sql.append("  zy_inspection_plan ip  ");
//		sql.append(" LEFT JOIN cbs_inspection_evaluate ie ON ip.id = ie.inspection_id ");
//		sql.append(" WHERE ");
//		sql.append(" 	YEAR (ip.end_time) = '").append(year).append("' ");
//		sql.append("	AND ip.company = '").append(ipCompany).append("' ");
//		sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
//		sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_1).append("' ");
//		Map<String,Object> cpAvgMap = super.findOneForJdbc(sql.toString());
//		Map<String, Object> rsMap = new HashMap<String, Object>();
//		if(StringUtil.isNotEmpty(cpAvgMap.get("avgScore"))){
//			rsMap.put("cpAvgScore", cpAvgMap.get("avgScore").toString());
//		}
//		aj.setAttributes(rsMap);
        aj.setObj(dataList);
    }

    public void getUserInspectionEvaluates(String userRole, Map<String, Object> returnMap) {
        TSUser user = ResourceUtil.getSessionUserName();
        TSUserOrg userOrg = super.findUniqueByProperty(TSUserOrg.class, "tsUser.id", user.getId());

        StringBuffer baseSql = new StringBuffer();
        baseSql.append(" SELECT ");
        baseSql.append(" 	IFNULL(ROUND(TRUNCATE (AVG(ie.last_evaluate), 2),1),0) AS avgScore, ");//保留2位小数，四舍五入 保留一位小数
        baseSql.append("	COUNT(ie.id) AS ieTotal ");
        baseSql.append(" FROM ");
        baseSql.append(" 	cbs_inspection_evaluate ie  ");
        baseSql.append(" LEFT JOIN zy_inspection_plan ip ON ip.id = ie.inspection_id ");
        baseSql.append(" WHERE ");
        baseSql.append(" 	TIMESTAMPDIFF(DAY ,ie.update_date, NOW()) < 30 ");
        baseSql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
        baseSql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_1).append("' ");

        StringBuffer countIpSql = new StringBuffer();
        countIpSql.append(" SELECT ");
        countIpSql.append(" 	COUNT(1) AS total ");
        countIpSql.append(" FROM ");
        countIpSql.append(" 	zy_inspection_plan ip ");
        countIpSql.append(" WHERE ");
        String states = "'" + StaticValue.DIC_PLAN_STATE_SEND_TO_THIRDPARTY + "'," +
                "'" + StaticValue.DIC_PLAN_STATE_THIRDPARTY_ASSIGNED + "'," +
                "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_REFUSED + "'," +
                "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_ASSIGNED + "'," +
                "'" + StaticValue.DIC_PLAN_STATE_ENGINEER_IN_IMPLEMENT + "'," +
                "'" + StaticValue.DIC_PLAN_STATE_AREA_ENGINEER_REFUSED + "'," +
                "'" + StaticValue.DIC_PLAN_STATE_AREA_ENGINEER_ASSIGNED + "'," +
                "'" + StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE + "'";
        int countCompanyIp = zyCommonService.getCountBySql(countIpSql.toString() + " ip.company='" + userOrg.getTsDepart().getOrgCode() + "' AND ip.state IN (" + states + ")");
        returnMap.put("countCompanyIp", countCompanyIp);

        StringBuffer sql = new StringBuffer();
        sql.append("	AND ip.company='").append(userOrg.getTsDepart().getOrgCode()).append("' ");
        /**
         *  a)	第三方商务角色显示影院对该公司工程师完成的巡检任务的“巡检满意度评分”的平均分数、评价总数和任务总数。
         b)	第三方区域工程师和普通工程师角色显示影院对其完成的巡检任务的“巡检满意度评分”的平均分数、本公司平均分数。低于和高于公司平均分的显示不同颜色。
         c)	平均分数、评价总数只统计最近30天的。
         */
        Map<String, Object> companyIeMap = super.findOneForJdbc(baseSql.toString() + sql.toString());
        returnMap.put("companyAvgScore", companyIeMap.get("avgScore").toString());
        returnMap.put("companyIeTotal", companyIeMap.get("ieTotal").toString());
        if (userRole.equals(StaticValue.RULE_CODE_AREA_ENGINEER) || userRole.equals(StaticValue.RULE_CODE_THIRDPARTY_ENGINEER)) {
            List<ZyEngineerEntity> egs = super.findByProperty(ZyEngineerEntity.class, "tsBaseUser.id", user.getId());
            ZyEngineerEntity engineer = new ZyEngineerEntity();
            if (null != egs && egs.size() > 0) {
                engineer = egs.get(0);
            }
            sql = new StringBuffer();
            sql.append("	AND ip.engineer='").append(engineer.getId()).append("' ");
            Map<String, Object> egIeMap = super.findOneForJdbc(baseSql.toString() + sql.toString());
            returnMap.put("avgScore", egIeMap.get("avgScore").toString());
            returnMap.put("ieTotal", egIeMap.get("ieTotal").toString());
            int countEgIp = zyCommonService.getCountBySql(countIpSql.toString() + " ip.engineer='" + engineer.getId() + "' ");
            returnMap.put("countEgIp", countEgIp);
        }
    }

    @Override
    public List<Map<String, Object>> queryUserIes(HttpServletRequest req) {
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        TSUser user = ResourceUtil.getSessionUserName();
        List<TSRoleUser> tsRoleUser = super.findHql("from TSRoleUser where TSUser.id = ?", user.getId());
        StringBuffer sql = new StringBuffer();
        if (null != tsRoleUser && tsRoleUser.size() > 0) {
            for (TSRoleUser rsRU : tsRoleUser) {
                if (rsRU.getTSRole().getRoleCode().equals(StaticValue.RULE_CODE_THIRDPARTY_HEADER)) {//商务-查询公司所有工程师平均分
                    TSUserOrg userOrg = super.findUniqueByProperty(TSUserOrg.class, "tsUser.id", user.getId());
                    sql.append(" SELECT ");
                    sql.append(" 	IFNULL(ROUND(TRUNCATE (AVG(ie.last_evaluate), 2),1),0) AS egAvgScore, eg.`name` AS egName, ");
                    sql.append(" 	CASE eg.is_area_engineer WHEN 1 THEN '区域工程师' ELSE '工程师' END AS isAeg, ");
                    sql.append("	IFNULL(u.head_file_log_id,'') AS headFileLogId, ");
                    sql.append("	COUNT(ie.id) AS ieCount ");
                    sql.append(" FROM ");
                    sql.append(" 	cbs_inspection_evaluate ie ");
                    sql.append(" LEFT JOIN zy_inspection_plan ip ON ip.id = ie.inspection_id ");
                    sql.append(" LEFT JOIN zy_engineer eg on eg.id = ip.engineer ");
                    sql.append(" LEFT JOIN t_s_user u on u.id = eg.ts_base_user_id ");
                    sql.append(" WHERE ");
                    sql.append(" 	ip.company = '").append(userOrg.getTsDepart().getOrgCode()).append("' ");
                    sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
                    sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_1).append("' ");
                    sql.append(" GROUP BY ip.engineer ");
                    sql.append(" ORDER BY egAvgScore DESC ");
                    rsMapList = super.findForJdbc(sql.toString());
                    break;
                }
                if (rsRU.getTSRole().getRoleCode().equals(StaticValue.RULE_CODE_AREA_ENGINEER) || rsRU.getTSRole().getRoleCode().equals(StaticValue.RULE_CODE_THIRDPARTY_ENGINEER)) {//工程师 - 查询所有评价的详情
                    List<ZyEngineerEntity> egs = super.findByProperty(ZyEngineerEntity.class, "tsBaseUser.id", user.getId());
                    ZyEngineerEntity engineer = new ZyEngineerEntity();
                    if (null != egs && egs.size() > 0) {
                        engineer = egs.get(0);
                    }
                    sql.append(" SELECT ");
                    sql.append(" 	IFNULL(cm.`name`,'')AS cmName, ");
                    sql.append(" 	ie.last_evaluate AS lastEvaluate, ie.service_evaluate AS serviceEvaluate, ie.talk_evaluate AS talkEvaluate, ");
                    sql.append(" 	ie.effect_evaluate AS effectEvaluate ,ie.train_evaluate AS trainEvaluate, ie.effect_evaluate AS effectEvaluate, ie.skill_evaluate AS skillEvaluate, ");
                    sql.append(" 	ie.opinion, DATE_FORMAT(ie.update_date,'%Y-%m-%d') AS updateDate ");
                    sql.append(" FROM ");
                    sql.append(" 	cbs_inspection_evaluate ie ");
                    sql.append(" LEFT JOIN zy_inspection_plan ip ON ip.id = ie.inspection_id ");
                    sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
                    sql.append(" LEFT JOIN zy_cinema cm ON cm.id = es.belong_cinema_id ");
                    sql.append(" WHERE ip.engineer = '").append(engineer.getId()).append("' ");
                    sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
                    sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_1).append("' ");
                    sql.append(" ORDER BY ie.create_date DESC ");
                    rsMapList = super.findForJdbc(sql.toString());
                    break;
                }
            }
        }
        return rsMapList;
    }

    public void doEvalute(CbsInspectionEvaluateEntity evalute) {
        if(evalute.getBusType().equals(StaticValue.DIC_EVALUATE_BUS_TYPE_1)){
            if (oConvertUtils.isEmpty(evalute.getInspection().getId())) {
                return;
            }
            evalute.setEvaluateState(evalute.getEvaluateState());
            // Add Start 增加评价人和评价时间  Amy.Li 2018-02-26
            TSUser user = ResourceUtil.getSessionUserName();
            evalute.setEvaluateName(user.getRealName());
            Date dEvaluateTime = new Date();
            evalute.setEvaluateTime(dEvaluateTime);
            // Add End 增加评价人和评价时间  Amy.Li 2018-02-26
            saveOrUpdate(evalute);
            ZyInspectionPlanEntity inspection = get(ZyInspectionPlanEntity.class, evalute.getInspection().getId());
            if (inspection == null) {
                return;
            }

            if (inspection.getEquipmentSpare().getZyCinema() == null) {
                return;
            }
            /*
             * 同一影院
             * 统一巡检工程师
             * 已完成巡检
             * */
            List<ZyInspectionPlanEntity> inspections = findHql("from ZyInspectionPlanEntity where state=? and equipmentSpare.zyCinema.id=? and engineer.id=? and evaluate.evaluateState=0",
                    StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE,
                    inspection.getEquipmentSpare().getZyCinema().getId(),
                    inspection.getEngineer().getId()
            );

            for (ZyInspectionPlanEntity insp : inspections) {
                CbsInspectionEvaluateEntity cie = insp.getEvaluate();
                cie.setEffectEvaluate(evalute.getEffectEvaluate());
                cie.setLastEvaluate(evalute.getLastEvaluate());
                cie.setServiceEvaluate(evalute.getServiceEvaluate());
                cie.setSkillEvaluate(evalute.getSkillEvaluate());
                cie.setTalkEvaluate(evalute.getTalkEvaluate());
                cie.setTrainEvaluate(evalute.getTrainEvaluate());
                cie.setEvaluateState(evalute.getEvaluateState());
                cie.setEvaluateType(evalute.getEvaluateType());
                cie.setOpinion(evalute.getOpinion());
                //2017-05-08 xiul 新增评价人 评价时间 字段，由updateName和updateDate改为evaluateName 和evaluateTime
                cie.setEvaluateName(user.getRealName());
                cie.setEvaluateTime(dEvaluateTime);
                saveOrUpdate(cie);
            }
        }else{
            TSUser user = ResourceUtil.getSessionUserName();
            Date dEvaluateTime = new Date();
            CbsInspectionEvaluateEntity cie = get(CbsInspectionEvaluateEntity.class,evalute.getId());
            cie.setEvaluateState(evalute.getEvaluateState());
            cie.setEvaluateName(user.getRealName());
            cie.setEvaluateTime(dEvaluateTime);
            cie.setEffectEvaluate(evalute.getEffectEvaluate());
            cie.setLastEvaluate(evalute.getLastEvaluate());
            cie.setServiceEvaluate(evalute.getServiceEvaluate());
            cie.setSkillEvaluate(evalute.getSkillEvaluate());
            cie.setTalkEvaluate(evalute.getTalkEvaluate());
            cie.setTrainEvaluate(evalute.getTrainEvaluate());
            cie.setEvaluateState(evalute.getEvaluateState());
            cie.setEvaluateType(evalute.getEvaluateType());
            cie.setOpinion(evalute.getOpinion());
            saveOrUpdate(cie);
        }



    }

    @Override
    public void createInspectionEvaluate(String inspectionPlanId) {
        CbsInspectionEvaluateEntity cie = new CbsInspectionEvaluateEntity();
        cie.setEffectEvaluate(0);
        cie.setLastEvaluate(0);
        cie.setServiceEvaluate(0);
        cie.setSkillEvaluate(0);
        cie.setTalkEvaluate(0);
        cie.setTrainEvaluate(0);
        cie.setBusType(StaticValue.DIC_EVALUATE_BUS_TYPE_1);
        cie.setEvaluateState(StaticValue.DIC_EVALUATE_STATE_0);
        ZyInspectionPlanEntity zyIp = get(ZyInspectionPlanEntity.class, inspectionPlanId);
        if (null != zyIp && StringUtil.isNotEmpty(zyIp)) {
        	cie.setInspection(zyIp);
            cie.setCinemaName(zyIp.getEquipmentSpare().getZyCinema().getName());
        } else {
        	zyIp = new ZyInspectionPlanEntity();
	        zyIp.setId(inspectionPlanId);
	        cie.setInspection(zyIp);
        }
        super.save(cie);
    }

    /*自动创建回访单*/
    public void createServiceEvaluate(String serviceId, String type) {
        CbsInspectionEvaluateEntity cie = new CbsInspectionEvaluateEntity();
        cie.setEffectEvaluate(0);
        cie.setLastEvaluate(0);
        cie.setServiceEvaluate(0);
        cie.setSkillEvaluate(0);
        cie.setTalkEvaluate(0);
        cie.setTrainEvaluate(0);
        cie.setEvaluateState(StaticValue.DIC_EVALUATE_STATE_0);
        if (type.equals(StaticValue.DIC_EVALUATE_BUS_TYPE_2)) {
            cie.setBusType(StaticValue.DIC_EVALUATE_BUS_TYPE_2);
        }
        if (type.equals(StaticValue.DIC_EVALUATE_BUS_TYPE_3)) {
            cie.setBusType(StaticValue.DIC_EVALUATE_BUS_TYPE_3);
        }
        CbsServiceEntity cbs = get(CbsServiceEntity.class, serviceId);
        if (StringUtil.isNotEmpty(cbs)) {
            cie.setCbsService(cbs);
            cie.setCinemaName(cbs.getZyCinemaName());
            cie.setLinkman(cbs.getContactsName());
            cie.setLinkphone(cbs.getContactsPhone());
            if(StringUtil.isNotEmpty(cbs.getZyCinemaId())){
                cie.setBelongCinemaId(cbs.getZyCinemaId().getId());
            }
        }

        super.save(cie);
    }

    @Override
    public void datagridOfIeListOfIeReport(HttpServletRequest request, DataGrid dataGrid) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	cm.id, cm.`name` AS cmName,COUNT(es.id) AS cmEsCount, ");
        sql.append("	FORMAT(AVG(ie.last_evaluate),1) AS avgLastEvaluate, ");
        sql.append("	FORMAT(AVG(ie.service_evaluate),1) AS avgServiceEvaluate, ");
        sql.append("	FORMAT(AVG(ie.talk_evaluate),1) AS avgTalkEvaluate, ");
        sql.append("	FORMAT(AVG(ie.effect_evaluate),1) AS avgEffectEvaluate, ");
        sql.append("	FORMAT(AVG(ie.train_evaluate),1) AS avgTrainEvaluate, ");
        sql.append("	FORMAT(AVG(ie.skill_evaluate),1) AS avgSkillEvaluate ");
        sql.append(" FROM  ");
        sql.append(" 	cbs_inspection_evaluate ie  ");
        sql.append(" 	INNER JOIN zy_inspection_plan ip ON ip.id = ie.inspection_id  ");
        sql.append(" 	LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id  ");
        sql.append(" 	LEFT JOIN zy_cinema cm ON cm.id = es.belong_cinema_id ");

        String score = request.getParameter("score");//score 对应 1分 2分 ...
        String ipCompany = request.getParameter("ipCompany");//巡检服务商（可能没有选择）
        String year = request.getParameter("year");//年份

        String month = request.getParameter("month");//月份
        String egId = request.getParameter("egId");//工程师ID
        //month 和 egId只会同时存在一个（分别对应第三方满意度tab和工程师满意度tab）

        Map<String, String[]> paramsMap = request.getParameterMap();
        sql.append(" WHERE ");
        sql.append(" 	!ISNULL(cm.id) ");
        /*---服务评价报表带来的条件---*/
        sql.append(" AND ie.last_evaluate =").append(score);
        sql.append(" AND YEAR(ip.end_time) =").append(year);
        if (StringUtil.isNotEmpty(month)) {
            sql.append(" AND MONTH(ip.end_time) =").append(month);
        } else if (StringUtil.isNotEmpty(egId)) {
            sql.append(" AND ip.engineer = '").append(egId).append("' ");
        }
        if (StringUtil.isNotEmpty(ipCompany)) {
            sql.append(" AND ip.company = '").append(ipCompany).append("' ");
        }
        /*---服务评价报表带来的条件 end---*/
        if (StringUtil.isNotEmpty(paramsMap.get("cmName")) && StringUtil.isNotEmpty(paramsMap.get("cmName")[0])) {
            sql.append(" AND cm.name LIKE '%").append(paramsMap.get("cmName")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("planId")) && StringUtil.isNotEmpty(paramsMap.get("planId")[0])) {
            sql.append(" AND ip.plan_id LIKE '%").append(paramsMap.get("planId")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("updateName")) && StringUtil.isNotEmpty(paramsMap.get("updateName")[0])) {
            sql.append(" AND ie.update_name LIKE '%").append(paramsMap.get("updateName")[0].toString()).append("%' ");
        }

        sql.append(" GROUP BY ");
        sql.append(" 	cm.id ");
        sql.append(" ORDER BY ");
        sql.append(" 	avgLastEvaluate ");
        System.out.println(sql);
        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    @Override
    public void datagridOfIeListOfIeRepairReport(HttpServletRequest request, DataGrid dataGrid) {
        StringBuffer sql = new StringBuffer();
        String score = request.getParameter("score");//score 对应 1分 2分 ...
        sql.append(" SELECT ");
//        sql.append(" 	cm.id, ser.zy_cinema_name AS cmName,IFNULL(ser.equipment_num,'')  AS cmEsCount, ");
        sql.append(" 	cm.id, ser.zy_cinema_name AS cmName, ");
        sql.append(" IFNULL((\n" +
                "\tSELECT\n" +
                "\t\tSUM(ead.total)\n" +
                "\tFROM\n" +
                "\t\tcbs_equipment_apply_detail ead\n" +
                "\tLEFT JOIN cbs_equipment_apply cea ON cea.id = ead.apply_id\n" +
                "\tLEFT JOIN cbs_service ser1 ON ser1.id = cea.service_id\n" +
                "\tWHERE\n" +
                "\t\tser1.id in (\n" +
                "\t\t\t\t\tSELECT\n" +
                "\t\t\t\t\t\tie1.cbsservice_id\n" +
                "\t\t\t\t\tFROM\n" +
                "\t\t\t\t\t\t`cbs_inspection_evaluate` ie1\n" +
                "\t\t\t\t\tINNER JOIN cbs_service ser2 ON ser2.id = ie1.cbsservice_id\n" +
                "\t\t\t\t\tWHERE\n" +
                "\t\t\t\t\t\tser2.zy_cinema_name = ser.zy_cinema_name\n" +
                "\n  AND ie1.last_evaluate='"+score+"'" +
                "\n" +
                ")\n" +
                "),0)  AS cmEsCount, ");
        sql.append("	FORMAT(AVG(ie.last_evaluate),1) AS avgLastEvaluate, ");
        sql.append("	FORMAT(AVG(ie.service_evaluate),1) AS avgServiceEvaluate, ");
        sql.append("	FORMAT(AVG(ie.talk_evaluate),1) AS avgTalkEvaluate, ");
        sql.append("	FORMAT(AVG(ie.effect_evaluate),1) AS avgEffectEvaluate, ");
        sql.append("	FORMAT(AVG(ie.train_evaluate),1) AS avgTrainEvaluate, ");
        sql.append("	FORMAT(AVG(ie.skill_evaluate),1) AS avgSkillEvaluate ");
        sql.append(" FROM  ");
        sql.append(" 	cbs_inspection_evaluate ie  ");
        sql.append(" 	INNER JOIN cbs_service ser ON ser.id = ie.cbsservice_id  ");
        sql.append(" 	LEFT JOIN zy_cinema cm ON cm.id = ser.zy_cinema_id ");

        String ipCompany = request.getParameter("ipCompany");//服务单位（可能没有选择）
        String year = request.getParameter("year");//年份

        String month = request.getParameter("month");//月份
        String egId = request.getParameter("egId");//工程师ID
        //month 和 egId只会同时存在一个（分别对应第三方满意度tab和工程师满意度tab）

        Map<String, String[]> paramsMap = request.getParameterMap();
        sql.append(" WHERE 1=1 ");
        /*---服务评价报表带来的条件---*/
        sql.append(" AND ie.last_evaluate =").append(score);
        sql.append(" AND YEAR(ser.close_time) =").append(year);
        if (StringUtil.isNotEmpty(month)) {
            sql.append(" AND MONTH(ser.close_time) =").append(month);
        } else if (StringUtil.isNotEmpty(egId)) {
            sql.append(" AND ser.engineer_id = '").append(egId).append("' ");
        }
        if (StringUtil.isNotEmpty(ipCompany)) {
            sql.append(" AND ser.service_provider_code = '").append(ipCompany).append("' ");
        }
        /*---服务评价报表带来的条件 end---*/
        if (StringUtil.isNotEmpty(paramsMap.get("cmName")) && StringUtil.isNotEmpty(paramsMap.get("cmName")[0])) {
            sql.append(" AND ser.zy_cinema_name LIKE '%").append(paramsMap.get("cmName")[0].toString()).append("%' ");
        }
        if (StringUtil.isNotEmpty(paramsMap.get("updateName")) && StringUtil.isNotEmpty(paramsMap.get("updateName")[0])) {
            sql.append(" AND ser.update_name LIKE '%").append(paramsMap.get("updateName")[0].toString()).append("%' ");
        }

        sql.append(" GROUP BY ");
        sql.append(" 	cm.id ");
        sql.append(" ORDER BY ");
        sql.append(" 	avgLastEvaluate ");
        System.out.println("sql-----"+sql);
        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    @Override
    public void exportIe(HttpServletRequest request, HttpServletResponse response) {
        OutputStream out = null;
        try {
            String fileName = "回访评价_CBS_" + DateUtils.date2Str(new Date(), DateUtils.date_sdf) + ".xlsx";
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/x-msdownload;");
            response.setHeader("Content-disposition", "attachment; filename=" + (new String(fileName.getBytes("gb2312"), "ISO8859-1")));
            out = response.getOutputStream();
            String[] ids = oConvertUtils.getString(request.getParameter("ids")).split(",");
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT\n" +
                    "\t*\n" +
                    "FROM\n" +
                    "\t(  SELECT ie.id,");  /*查巡检数据*/
            sql.append(" 	IFNULL(cm.name,'') as cinemaName, CASE WHEN zc.inp_con_user IS NULL THEN '' ELSE zc. inp_con_user END AS contactName, CASE WHEN zc.inp_con_user_tel IS NULL THEN '' ELSE zc.inp_con_user_tel END AS phone,CASE WHEN ip.end_time IS NULL THEN '' ELSE ip.end_time END AS actualDate, ");
            sql.append("	tp.typename AS typeName1, ip.plan_id, dp.departname, eg.name AS name2, case ie.evaluate_state when '0' then '未评价' when '1' then '已评价' END AS typeName2, ");
            /*sql.append(" 	ie.last_evaluate, ie.service_evaluate, ie.talk_evaluate, ie.effect_evaluate, ie.train_evaluate, ie.opinion, ");*/
            sql.append("	ie.evaluate_name,DATE_FORMAT(ie.evaluate_time,'%Y-%m-%d %H:%i:%s'),  ie.remark,\n" +
                    "ie.last_evaluate,\n" +
                    "ie.OPINION");
            sql.append(" FROM ");
            sql.append(" 	cbs_inspection_evaluate ie ");
            sql.append(" INNER JOIN zy_inspection_plan ip ON ie.inspection_id = ip.id ");
            sql.append(" left join zy_inspection_report zc on ip.id = zc.inspection_plan_id ");
            sql.append(" LEFT JOIN zy_equipment_spare es ON ip.equipment_spare_id = es.id ");
            sql.append(" LEFT JOIN zy_cinema cm ON es.belong_cinema_id = cm.id ");
            sql.append(" LEFT JOIN zy_engineer eg ON ip.engineer = eg.id ");
            sql.append(" LEFT JOIN t_s_depart dp ON ip.company = dp.org_code ");
            sql.append(" LEFT JOIN zy_contacts ct ON ct.belong_cinema = cm.id AND ct.position = '").append(StaticValue.DIC_CONTACT_POSITION_C_MANAGER).append("' ");
            sql.append(" LEFT JOIN t_s_typegroup tg ON tg.typegroupcode = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE).append("' ");
            sql.append(" LEFT JOIN t_s_type tp ON tp.typecode = ie.bus_type AND tg.id = tp.typegroupid ");
            sql.append(" LEFT JOIN t_s_typegroup tg1 ON tg1.typegroupcode = '").append(StaticValue.DIC_EVALUATE_STATE).append("' ");
            sql.append(" LEFT JOIN t_s_type tp1 ON tp1.typecode = ie.evaluate_state AND tg1.id = tp1.typegroupid ");
            sql.append(" WHERE ");
            sql.append(" 	ie.id IN ( ");
            for (int i = 0; i < ids.length; i++) {
                if (i == ids.length - 1) {
                    sql.append("'" + ids[i] + "'");
                } else {
                    sql.append("'" + ids[i] + "',");
                }
            }
            sql.append(" 	) ");
            sql.append(" GROUP BY ");
            sql.append(" 	ie.inspection_id ");
            sql.append(" UNION ");
            sql.append("SELECT\n");   /*查自动创建的服务单数据*/
            sql.append("\tt1.id, CASE\n");
            sql.append("WHEN t4.`name` IS NULL THEN\n");
            sql.append("\t''\n");
            sql.append("ELSE\n");
            sql.append("\tt4. NAME\n");
            sql.append("END AS cinemaName,\n");
            sql.append(" IFNULL(t1.`link_man`, t4.`name`) AS name1,\n");
            sql.append(" IFNULL(t1.`link_phone`,c.cellphone1) AS cellphone1,\n");
            sql.append("\n");
            sql.append("IFNULL(t2.close_time, IFNULL(t2.close_time,'')) AS actualDate,\n");
            sql.append(" tp.typename AS typeName1,\n");
            sql.append(" t2. CODE,\n");
            sql.append(" t6.departname,\n");
            sql.append(" zy.`name` AS name2,\n");
            sql.append(" case t1.evaluate_state when '0'then '未评价' when '1' then '已评价' END AS typeName2,\n");
            /*sql.append(" t1.last_evaluate,\n");
            sql.append(" t1.service_evaluate,\n");
            sql.append(" t1.talk_evaluate,\n");
            sql.append(" t1.effect_evaluate,\n");
            sql.append(" t1.train_evaluate,\n");
            sql.append(" t1.opinion,\n");*/
            sql.append(" t1.evaluate_name,\n");
            sql.append(" DATE_FORMAT(\n");
            sql.append("\tt1.evaluate_time,\n");
            sql.append("\t'%Y-%m-%d %H:%i:%s'\n");
            sql.append("),\n");
            sql.append(" t1.remark,\n" +
                    "t1.last_evaluate,\n" +
                    "t1.OPINION\n");
            sql.append("FROM\n");
            sql.append("\tcbs_inspection_evaluate t1\n");
            sql.append("INNER JOIN cbs_service t2 ON t1.cbsservice_id = t2.id\n");
            sql.append("LEFT JOIN zy_cinema t4 ON t2.zy_cinema_id = t4.id\n");
            sql.append("LEFT JOIN t_s_depart t6 ON t6.org_code = t2.service_provider_code \n");
            sql.append("LEFT JOIN  zy_engineer zy ON zy.id = t2.engineer_id \n");
            sql.append("LEFT JOIN zy_contacts c ON c.id = t2.zy_contacts_id\n");
            sql.append("LEFT JOIN CBS_SCENE_REPORT r1 ON r1.service_id = t1.id\n");
            sql.append("LEFT JOIN t_s_typegroup tg ON tg.typegroupcode = 'evaluate_bus_type'\n");
            sql.append("LEFT JOIN t_s_type tp ON tp.typecode = t1.bus_type\n");
            sql.append("AND tg.id = tp.typegroupid\n");
            sql.append("LEFT JOIN t_s_typegroup tg1 ON tg1.typegroupcode = 'evaluate_state'\n");
            sql.append("LEFT JOIN t_s_type tp1 ON tp1.typecode = t1.evaluate_state\n");
            sql.append("AND tg1.id = tp1.typegroupid\n");
            sql.append("WHERE\n");
            sql.append("  t2.`code` IS NULL AND");
            sql.append(" 	t1.id IN ( ");
            for (int i = 0; i < ids.length; i++) {
                if (i == ids.length - 1) {
                    sql.append("'" + ids[i] + "'");
                } else {
                    sql.append("'" + ids[i] + "',");
                }
            }
            sql.append(" 	) ");
            sql.append(" GROUP BY ");
            sql.append(" 	t1.id ");
            sql.append(" UNION ");   /*查手动创建，含有服务单code的数据*/
            sql.append(" SELECT\n" +
                    "\tt1.id, CASE\n" +
                    "WHEN t1.`cinema_name` IS NULL THEN\n" +
                    "\t''\n" +
                    "ELSE\n" +
                    "\tt1.`cinema_name`\n" +
                    "END AS cinemaName,\n" +
                    " t1.link_man AS name1,\n" +
                    " t1.link_phone,'' AS actualDate,\n" +
                    "\n" +
                    " tp.typename AS typeName1,\n" +
                    " t2. CODE,\n" +
                    " t6.departname,\n" +
                    " zy.`name` AS name2,\n" +
                    " case t1.evaluate_state when '0'then '未评价' when '1' then '已评价' END AS typeName2,\n" +
                    /*" t1.last_evaluate,\n" +
                    " t1.service_evaluate,\n" +
                    " t1.talk_evaluate,\n" +
                    " t1.effect_evaluate,\n" +
                    " t1.train_evaluate,\n" +
                    " t1.opinion,\n" +*/
                    " t1.evaluate_name,\n" +
                    " DATE_FORMAT(\n" +
                    "\tt1.evaluate_time,\n" +
                    "\t'%Y-%m-%d %H:%i:%s'\n" +
                    "),\n" +
                    " t1.remark,\n" +
                    "t1.last_evaluate,\n" +
                    "t1.OPINION\n" +
                    "FROM\n" +
                    "\tcbs_inspection_evaluate t1\n" +
                    "INNER JOIN cbs_service t2 ON t1.cbs_code = t2.`code`\n" +
                    "LEFT JOIN zy_cinema t4 ON t2.zy_cinema_id = t4.id\n" +
                    "LEFT JOIN t_s_depart t6 ON t6.org_code = t2.service_provider_code\n" +
                    "\tLEFT JOIN  zy_engineer zy ON zy.id = t2.engineer_id " +
                    "LEFT JOIN zy_contacts c ON c.id = t2.zy_contacts_id\n" +
                    "LEFT JOIN CBS_SCENE_REPORT r1 ON r1.service_id = t1.id\n" +
                    "LEFT JOIN t_s_typegroup tg ON tg.typegroupcode = 'evaluate_bus_type'\n" +
                    "LEFT JOIN t_s_type tp ON tp.typecode = t1.bus_type\n" +
                    "AND tg.id = tp.typegroupid\n" +
                    "LEFT JOIN t_s_typegroup tg1 ON tg1.typegroupcode = 'evaluate_state'\n" +
                    "LEFT JOIN t_s_type tp1 ON tp1.typecode = t1.evaluate_state\n" +
                    "AND tg1.id = tp1.typegroupid\n" );
            sql.append("WHERE  t1.cbsservice_id is null  and \n");
            sql.append("t1.id IN ( ");
            for (int i = 0; i < ids.length; i++) {
                if (i == ids.length - 1) {
                    sql.append("'" + ids[i] + "'");
                } else {
                    sql.append("'" + ids[i] + "',");
                }
            }
            sql.append(" 	) ");
            sql.append(" GROUP BY ");
            sql.append(" 	t1.id ");
            sql.append(" UNION ");
            sql.append("SELECT\n");    /*查手动创建，不含服务单code的数据*/
            sql.append("\tt1.id, CASE\n");
            sql.append("WHEN t4.`name` IS NULL THEN\n");
            sql.append("\t''\n");
            sql.append("ELSE\n");
            sql.append("\tt4. NAME\n");
            sql.append("END AS cinemaName,\n");
            sql.append(" t1.link_man AS name1,\n");
            sql.append(" t1.link_phone as phone,\n");
            sql.append(" CASE\n" +
                    "            WHEN t2.close_time IS NULL THEN\n" +
                    "            ''\n" +
                    "            ELSE\n" +
                    "            t2.close_time\n" +
                    "            END AS actualDate,\n");
            sql.append(" tp.typename AS typeName1,\n");
            sql.append(" IFNULL(\n" +
                    "\tt2.`code`,\n" +
                    "\tIFNULL(t1.cbs_code, '')\n" +
                    "\t) AS planId,\n");
            sql.append(" t6.departname as departname,\n");
            sql.append(" zy.name AS name2,\n");
            sql.append(" case t1.evaluate_state when '0'then '未评价' when '1' then '已评价' END AS typeName2,\n");
            /*sql.append(" t1.last_evaluate,\n");
            sql.append(" t1.service_evaluate,\n");
            sql.append(" t1.talk_evaluate,\n");
            sql.append(" t1.effect_evaluate,\n");
            sql.append(" t1.train_evaluate,\n");
            sql.append(" t1.opinion,\n");*/
            sql.append(" t1.evaluate_name,\n");
            sql.append(" DATE_FORMAT(\n");
            sql.append("\tt1.evaluate_time,\n");
            sql.append("\t'%Y-%m-%d %H:%i:%s'\n");
            sql.append("),\n");
            sql.append(" t1.remark,\n" +
                    "t1.last_evaluate,\n" +
                    "t1.OPINION\n");
            sql.append("FROM\n");
            sql.append("\tcbs_inspection_evaluate t1\n");
            sql.append("\tLEFT JOIN cbs_service ts2 ON t1.cbs_code = ts2.`code`\n" +
                    "left JOIN cbs_service t2 ON t1.cbsservice_id = t2.id \n");
            sql.append("LEFT JOIN zy_cinema t4 ON t1.belong_cinema_id = t4.id\n");
            sql.append("LEFT JOIN t_s_depart t6 ON t6.org_code = t2.service_provider_code\n" +
                    "LEFT JOIN zy_engineer zy ON zy.id = t2.engineer_id\n");
            sql.append("LEFT JOIN t_s_type tp1 ON tp1.typecode = t1.evaluate_state\n");
            sql.append(" LEFT JOIN t_s_typegroup tg ON tg.typegroupcode = 'evaluate_bus_type'\n" +
                    "LEFT JOIN t_s_type tp ON tp.typecode = t1.bus_type AND tg.id = tp.typegroupid\n");
            sql.append("WHERE\n");
//            sql.append(" t1.bus_type = 'type_4' and ");
            sql.append("t1.id IN ( ");
            for (int i = 0; i < ids.length; i++) {
                if (i == ids.length - 1) {
                    sql.append("'" + ids[i] + "'");
                } else {
                    sql.append("'" + ids[i] + "',");
                }
            }
            sql.append(" 	) ");
            sql.append(" GROUP BY ");
            sql.append(" 	t1.id  ) AS m\n" +
                    "GROUP BY\n" +
                    "\tm.id ORDER BY cinemaName desc");

            List<Map<String, Object>> reusltMaps = super.findForJdbc(sql.toString());
            TSUser user = ResourceUtil.getSessionUserName();
            systemService.addLog(user.getRealName()+"导出"+reusltMaps.size()+"条评价记录", Globals.Log_Type_OTHER, Globals.Log_Leavel_INFO);

            InputStream is = ZyInspectionPlanServiceImpl.class.getClassLoader().getResourceAsStream("jeecg/template/inspectionEvaluates.xlsx");
            XSSFWorkbook xssf = new XSSFWorkbook(is);
            XSSFSheet sheet = xssf.getSheetAt(0);
            int rowNum = 1;
            for (Map<String, Object> dataMap : reusltMaps) {
                XSSFRow row = sheet.createRow(rowNum);
                int cellNum = 0;
                for (String key : dataMap.keySet()) {
                    if(!key.equals("id")){
                        XSSFCell cell = row.createCell(cellNum);
                        cell.setCellValue(dataMap.get(key) != null ? dataMap.get(key).toString() : "");
                        cellNum++;
                    }

                }
                rowNum++;
            }
            xssf.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void queryIeDetialOfIeReportData(HttpServletRequest request, DataGrid dataGrid) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append("  	ie.id, ip.id AS ipId,");
        sql.append(" 	ip.plan_id AS planId,");
        sql.append("  	ie.last_evaluate AS lastEvaluate, ");
        sql.append("  	ie.service_evaluate AS serviceEvaluate, ");
        sql.append("  	ie.talk_evaluate AS talkEvaluate, ");
        sql.append("  	ie.effect_evaluate AS effectEvaluate, ");
        sql.append("  	ie.train_evaluate AS trainEvaluate, ");
        sql.append("  	ie.skill_evaluate AS skillEvaluate, ");
        sql.append("  	ie.evaluate_name AS evaluateName, ");
        sql.append("  	ie.evaluate_time AS evaluateTime ,");
        sql.append(" 	ie.opinion AS `option`,");
//		sql.append(" 	CASE WHEN ie.last_evaluate <= 3 THEN IFNULL(ie.opinion,'-') ELSE '-' END AS `option`,");
        sql.append(" 	eg.id AS egId,");
        sql.append(" 	eg.name AS egName");
        sql.append(" FROM ");
        sql.append("  	cbs_inspection_evaluate ie ");
        sql.append(" INNER JOIN zy_inspection_plan ip ON ip.id = ie.inspection_id");
        sql.append(" INNER JOIN zy_engineer eg ON eg.id = ip.engineer");
        sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
        sql.append(" LEFT JOIN zy_cinema cm ON cm.id = es.belong_cinema_id ");
        String cmId = request.getParameter("id");//影院ID

        String score = request.getParameter("score");//score 对应 1分 2分 ...
        String ipCompany = request.getParameter("ipCompany");//巡检服务商（可能没有选择）
        String year = request.getParameter("year");//年份

        String month = request.getParameter("month");//月份
        String egId = request.getParameter("egId");//工程师ID
        //month 和 egId只会同时存在一个（分别对应第三方满意度tab和工程师满意度tab）

        Map<String, String[]> paramsMap = request.getParameterMap();
        sql.append(" WHERE ");
        sql.append(" 	cm.id = '").append(cmId).append("' ");
        /*---服务评价报表带来的条件---*/
        sql.append(" AND ie.last_evaluate =").append(score);
        sql.append(" AND YEAR(ip.end_time) =").append(year);
        if (StringUtil.isNotEmpty(month)) {
            sql.append(" AND MONTH(ip.end_time) =").append(month);
        } else if (StringUtil.isNotEmpty(egId)) {
            sql.append(" AND ip.engineer = '").append(egId).append("' ");
        }
        if (StringUtil.isNotEmpty(ipCompany)) {
            sql.append(" AND ip.company = '").append(ipCompany).append("' ");
        }

        System.err.println(sql);
        /*---服务评价报表带来的条件 end---*/

        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    @Override
    public void queryEgDitailOfIeDetialOfIeReport(HttpServletRequest request, DataGrid dataGrid) {
        String year = request.getParameter("year");//年份
        String egId = request.getParameter("egId");//工程师ID

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	t.*, ");
        sql.append(" 	SUM(CASE WHEN avgLastEvaluate >= oth.avgOthers THEN 1 ELSE 0 END) AS rank ");
        sql.append(" FROM ");
        sql.append(" 	(SELECT ");
        sql.append(" 		eg.id AS egId, eg.`name` AS egName, dp.departname, ");
        sql.append(" 		FORMAT(AVG(ie.last_evaluate), 1) AS avgLastEvaluate, ");
        sql.append(" 		SUM(CASE WHEN last_evaluate <= 3 THEN 1 ELSE 0 END) AS blowThreeCount, ");
        sql.append(" 		company ");
        sql.append(" 	FROM ");
        sql.append(" 		cbs_inspection_evaluate ie ");
        sql.append(" 	INNER JOIN zy_inspection_plan ip ON ip.id = ie.inspection_id ");
        sql.append(" 	LEFT JOIN zy_engineer eg ON eg.id = ip.engineer ");
        sql.append(" 	LEFT JOIN t_s_depart dp ON dp.org_code = ip.company ");
        sql.append("	WHERE ");
        sql.append("		eg.id = '").append(egId).append("' ");
        /*---服务评价报表带来的条件---*/
        sql.append("		AND YEAR(ip.end_time) =").append(year);
        /*---服务评价报表带来的条件 end---*/
        sql.append(" )t ");
        sql.append(" LEFT JOIN ( ");
        sql.append(" 	SELECT ");
        sql.append(" 		FORMAT(AVG(ie.last_evaluate), 1) avgOthers, ip.company ");
        sql.append(" 	FROM ");
        sql.append(" 		cbs_inspection_evaluate ie ");
        sql.append(" 	INNER JOIN zy_inspection_plan ip ON ip.id = ie.inspection_id ");
        sql.append(" 	GROUP BY ");
        sql.append(" 		ip.engineer ");
        sql.append(" 	ORDER BY ");
        sql.append(" 		avgOthers ");
        sql.append(" ) oth ON oth.company = t.company ");

        System.out.println(sql);
        Map<String, String[]> paramsMap = request.getParameterMap();

        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    /**
     * 判断服务单完工时，是否需要自动创建评价单
     *
     * @param ServiceId
     * @return
     */
    public List<Map<String, Object>> getAssessService(String ServiceId) {
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n");
        sql.append(" cbs.id as id, CASE\n");
        sql.append("WHEN t1.`name` LIKE '%维修%'\n");
        sql.append("OR t2.`name` LIKE '%维修%' THEN\n");
        sql.append("\t 1\n");
        sql.append("END AS type1,\n");
        sql.append(" CASE\n");
        sql.append("WHEN t1.`name` LIKE '%咨询%'\n");
        sql.append("OR t2.`name` LIKE '%投诉%' THEN\n");
        sql.append("\t 2\n");
        sql.append("END  AS type2 , COUNT(e.id) as num \n");
        sql.append("FROM\n");
        sql.append("	cbs_service cbs\n");
        sql.append("LEFT JOIN cbs_service_type t1 ON t1.id = cbs.service_type_1\n");
        sql.append("LEFT JOIN cbs_service_type t2 ON t2.id = cbs.service_type_2\n");
        sql.append(" LEFT JOIN cbs_inspection_evaluate e on e.cbs_code = cbs.`code` or e.cbsservice_id = cbs.id\n");
        sql.append("WHERE\n");
        sql.append("	1 = 1\n");
        sql.append("AND ( (t1.`name` LIKE '%维修%' or t2.`name` LIKE '%维修%') OR (t1.`name` LIKE '%咨询%' and t2.`name` LIKE '%投诉%') )");
        sql.append("AND cbs.id = '").append(ServiceId).append("'");
        rsMapList = findForJdbc(sql.toString());
        return rsMapList;
    }

    @Override
    public boolean getIsTrue(String ServiceId) {
        boolean flag = false;
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n");
        sql.append("	SUM(one+two) as sum\n");
        sql.append("FROM\n");
        sql.append("	(\n");
        sql.append("		SELECT\n");
        sql.append("			COUNT(i.id) AS one\n");
        sql.append("		FROM\n");
        sql.append("			cbs_inspection_evaluate i\n");
        sql.append("		LEFT JOIN zy_inspection_plan p ON p.id = i.inspection_id\n");
        sql.append("		WHERE\n");
        sql.append("			p.plan_id = '").append(ServiceId).append("' ");
        sql.append("	) AS a,\n");
        sql.append("	(\n");
        sql.append("		SELECT\n");
        sql.append("			COUNT(i.id) AS two\n");
        sql.append("		FROM\n");
        sql.append("			cbs_inspection_evaluate i\n");
        sql.append("		LEFT JOIN cbs_service s ON s.id = i.cbsservice_id ");
        sql.append("		WHERE\n");
        sql.append("			s.`code` = '").append(ServiceId).append("' ");
        sql.append("	) AS b");
        rsMapList = findForJdbc(sql.toString());
        if (Integer.parseInt(rsMapList.get(0).get("sum").toString()) > 0) {
            flag = true;
        }
        return flag;
    }

    @Override
    public void assessService(String id) {
        if (StringUtil.isNotEmpty(id)) {
            List<Map<String, Object>> map = this.getAssessService(id);
            if (map.size() > 0) {
                if(StringUtil.isNotEmpty(map.get(0).get("num"))&&Integer.valueOf(String.valueOf(map.get(0).get("num")))==0){
                    if(StringUtil.isNotEmpty(map.get(0).get("type1"))&&String.valueOf(map.get(0).get("type1")).equals("1")){//维修
                        this.createServiceEvaluate(id, "type_2");
                    }else if(StringUtil.isNotEmpty(map.get(0).get("type2"))&&String.valueOf(map.get(0).get("type2")).equals("2")){  //投诉
                        this.createServiceEvaluate(id, "type_3");
                    }
                }
            }
        } else {
            throw new IllegalArgumentException("服务单id不能为空！");
        }
    }

    @Override
    public void queryEvaluateData(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

        Map param = new HashMap<> ();
        param.put ( "evaluateState", StaticValue.DIC_EVALUATE_STATE_0 );
        param.put ( "busType", StaticValue.DIC_EVALUATE_BUS_TYPE_2 );
        for (Object key : request.getParameterMap ().keySet ()) {
            if ((String[]) request.getParameterMap ().get ( key ) != null) {
                for (int i = 0; i < ((String[]) request.getParameterMap ().get ( key )).length; i++) {
                    if (((String[]) request.getParameterMap ().get ( key ))[i] != null) {
                        param.put ( key, ((String[]) request.getParameterMap ().get ( key ))[i].trim() );
                    }
                }
            }
        }

        wiseCoreService.selectDataGrid ( "selectEvaluate", param, dataGrid );
        /**
         * 单独查询 设备、CBS_EQUIPMENT_APPLY、cbs_equipment_apply_detail 三张表，设置关键字段的值
         */
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT MAX(id) AS id, eq1.service_id FROM CBS_EQUIPMENT_APPLY eq1 where eq1.`status` = 2 group by service_id ");
        List<Map<String, Object>> equipmentApplyMaps = systemService.findForJdbc(sql.toString());
        sql.setLength(0);
        sql.append(" SELECT id, name FROM zy_engineer ");
        List<Map<String, Object>> engMaps = systemService.findForJdbc(sql.toString());
        sql.setLength(0);
        sql.append(" SELECT ");
        sql.append("     MAX( eqd.id ) ,eq1.service_id ");
        sql.append(" FROM ");
        sql.append("     cbs_equipment_apply_detail eqd ");
        sql.append("     LEFT JOIN CBS_EQUIPMENT_APPLY eq1 ON eqd.apply_id = eq1.id ");
        sql.append("     LEFT JOIN zy_es_item es ON es.item = eqd.item ");
        sql.append(" WHERE ");
        sql.append("     es.is_track = '1' ");
        sql.append("     AND eq1.`status` = 2 ");
        sql.append(" GROUP BY ");
        sql.append("     eq1.service_id ");
        List<Map<String, Object>> equipmentApplyDetailMaps = systemService.findForJdbc(sql.toString());

        Map<String, Map> equipmentApplyMap = StringUtil.listToMapWithKey(equipmentApplyMaps, "service_id");
        Map<String, Map> engMap = StringUtil.listToMapWithKey(engMaps, "id");
        Map<String, Map> equipmentApplyDetailMap = StringUtil.listToMapWithKey(equipmentApplyDetailMaps, "service_id");

        Iterator it = dataGrid.getResults().iterator();
        Map<String, Map> mm = null,
                eaMap = null,
                eadMap = null;
        while(it.hasNext()){
            Map<String, Object> dataMap = (Map<String, Object>)it.next();
            //把sql中部分列的查询转移到单独查询
            if(StringUtil.isNotEmpty(dataMap.get("serviceId"))){
                eaMap = equipmentApplyMap.get(dataMap.get("serviceId"));
                dataMap.put("part", null != eaMap ? "是" : "否");
                eadMap = equipmentApplyDetailMap.get(dataMap.get("serviceId"));
                dataMap.put("keypart", null != eadMap ? "是" : "否");
            }
            if(StringUtil.isNotEmpty(dataMap.get("engineer"))){
            	mm = engMap.get(dataMap.get("engineer"));
                dataMap.put("engineer", null != mm ? mm.get("name") : "");
            }
        }

        TagUtil.datagrid ( response, dataGrid );
    }


    @Override
    public List<List<String>> queryIpRepairEvaluateReportDataOfTp(String year, String ipCompany, String egIds) {
        String egIdStr = "";
        if (StringUtil.isNotEmpty(egIds)) {
            egIdStr = "'" + egIds.replace(",", "','") + "'";
        }
        List<List<String>> dataList = new ArrayList<List<String>>();
        StringBuffer sql = new StringBuffer();//已有月份评价平均分
        sql.append(" SELECT ");
        sql.append("	MONTH (ser.close_time) AS ipMonth, ");
        sql.append("	IFNULL(TRUNCATE(AVG(ie.last_evaluate),2),0) AS avgScore, ");
        sql.append("	COUNT(ser.id) ipCount, ");
        sql.append("	COUNT(ie.id) ieCount, ");
        sql.append("	SUM(IF(last_evaluate = 5,1,0)) AS five, ");
        sql.append("	SUM(IF(last_evaluate = 4,1,0)) AS four, ");
        sql.append("	SUM(IF(last_evaluate = 3,1,0)) AS three, ");
        sql.append("	SUM(IF(last_evaluate = 2,1,0)) AS two, ");
        sql.append("	SUM(IF(last_evaluate = 1,1,0)) AS one, ");
        sql.append("	IFNULL(SUM(ie.last_evaluate),0) AS sumLastEvaluate ");
        sql.append(" FROM ");
        sql.append(" 	cbs_service ser ");
        sql.append(" LEFT JOIN cbs_inspection_evaluate ie ON ser.id = ie.cbsservice_id ");
        sql.append(" WHERE ");
        sql.append(" 	YEAR (ser.create_date) = '").append(year).append("' ");
        sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
        sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_2).append("' ");
        if (StringUtil.isNotEmpty(ipCompany)) {
            sql.append(" AND ser.service_provider_code = '").append(ipCompany).append("' ");
        }
        if (StringUtil.isNotEmpty(egIdStr)) {
            sql.append(" AND ser.engineer IN(").append(egIdStr).append(") ");
        }
        sql.append(" GROUP BY ipMonth ");
        sql.append(" ORDER BY ipMonth ");
        List<Map<String, Object>> dataMapList = super.findForJdbc(sql.toString());
        for (int r = 0; r < 8; r++) {
            List<String> rowList = new ArrayList<String>();
            if (r == 0) {
                rowList.add("评价平均分");
            } else if (r == 1) {
                rowList.add("评价总数");
            } else if (r == 2) {
                rowList.add("任务总数");
            } else if (r == 3) {
                rowList.add("评价5分的数量");
            } else if (r == 4) {
                rowList.add("评价4分的数量");
            } else if (r == 5) {
                rowList.add("评价3分的数量");
            } else if (r == 6) {
                rowList.add("评价2分的数量");
            } else if (r == 7) {
                rowList.add("评价1分的数量");
            }
            double rowTotal = 0;
            boolean had = false;
            double r1_ieCount = 0;//第一行总计用 ： 所有总体评价分数 / 评价数量
            int sumLastEvaluate = 0;//第一行总计用 ： 所有总体评价分数 / 评价数量
            for (int month = 1; month < 13; month++) {
                for (Map<String, Object> map : dataMapList) {
                    if (StringUtil.isNotEmpty(map.get("ipMonth"))&&month == Integer.valueOf(map.get("ipMonth").toString())) {
                        double cell = 0;
                        if (r == 0) {
                            cell = Double.valueOf(map.get("avgScore").toString());
                        } else if (r == 1) {
                            cell = Integer.valueOf(map.get("ipCount").toString());
                        } else if (r == 2) {
                            cell = Integer.valueOf(map.get("ieCount").toString());
                        } else if (r == 3) {
                            cell = Integer.valueOf(map.get("five").toString());
                        } else if (r == 4) {
                            cell = Integer.valueOf(map.get("four").toString());
                        } else if (r == 5) {
                            cell = Integer.valueOf(map.get("three").toString());
                        } else if (r == 6) {
                            cell = Integer.valueOf(map.get("two").toString());
                        } else if (r == 7) {
                            cell = Integer.valueOf(map.get("one").toString());
                        }
                        r1_ieCount += Integer.valueOf(map.get("ieCount").toString());//第一行总计用 ： 所有总体评价分数 / 评价数量
                        sumLastEvaluate += Integer.valueOf(map.get("sumLastEvaluate").toString());//第一行总计用 ： 所有总体评价分数 / 评价数量
                        rowTotal += cell;
                        rowList.add(cell + "");
                        had = true;
                        break;
                    }
                }
                if (!had) {
                    rowList.add("0");
                }
                had = false;
            }
            if (r == 0 && r1_ieCount != 0) {//第一行总计用 ： 所有总体评价分数 / 评价数量
                rowTotal = new BigDecimal(sumLastEvaluate / r1_ieCount).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
            }
            rowList.add(rowTotal + "");
            rowTotal = 0;
            dataList.add(rowList);
        }
        return dataList;
    }

    @Override
    public void queryIeRepairDetialOfIeReportData(HttpServletRequest request, DataGrid dataGrid) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append("  	ie.id, ser.id AS serId,");
        sql.append(" 	ser.`code` AS serCode,");
        sql.append("  	ie.last_evaluate AS lastEvaluate, ");
        sql.append("  	ie.service_evaluate AS serviceEvaluate, ");
        sql.append("  	ie.talk_evaluate AS talkEvaluate, ");
        sql.append("  	ie.effect_evaluate AS effectEvaluate, ");
        sql.append("  	ie.train_evaluate AS trainEvaluate, ");
        sql.append("  	ie.skill_evaluate AS skillEvaluate, ");
        sql.append("  	ie.evaluate_name AS evaluateName, ");
        sql.append("  	ie.evaluate_time AS evaluateTime ,");
        sql.append(" 	ie.opinion AS `option`,");
//		sql.append(" 	CASE WHEN ie.last_evaluate <= 3 THEN IFNULL(ie.opinion,'-') ELSE '-' END AS `option`,");
        sql.append(" 	eg.id AS egId,");
        sql.append(" 	ifnull(eg.name,'') AS egName");
//        sql.append(" 	eg.name AS egName");
        sql.append(" FROM ");
        sql.append("  	cbs_inspection_evaluate ie ");
        sql.append(" LEFT JOIN cbs_service ser ON ser.id = ie.cbsservice_id");
        sql.append(" LEFT JOIN zy_engineer eg ON eg.id = ser.engineer_id");
        String cmId = request.getParameter("id");//影院ID
        String cmName = request.getParameter("cmName");//影院名称

        String score = request.getParameter("score");//score 对应 1分 2分 ...
        String ipCompany = request.getParameter("ipCompany");//维修单位（可能没有选择）
        String year = request.getParameter("year");//年份

        String month = request.getParameter("month");//月份
        String egId = request.getParameter("egId");//工程师ID
        //month 和 egId只会同时存在一个（分别对应第三方满意度tab和工程师满意度tab）

        Map<String, String[]> paramsMap = request.getParameterMap();
        sql.append(" WHERE 1=1 ");
        sql.append(" 	AND zy_cinema_name = '").append(cmName).append("' ");
        /*---服务评价报表带来的条件---*/
        sql.append(" AND ie.last_evaluate =").append(score);
        sql.append(" AND YEAR(ser.close_time) =").append(year);
        if (StringUtil.isNotEmpty(month)) {
            sql.append(" AND MONTH(ser.close_time) =").append(month);
        } else if (StringUtil.isNotEmpty(egId)) {
            sql.append(" AND ser.engineer_id = '").append(egId).append("' ");
        }
        if (StringUtil.isNotEmpty(ipCompany)) {
            sql.append(" AND service_provider_code = '").append(ipCompany).append("' ");
        }

        System.err.println(sql);
        /*---服务评价报表带来的条件 end---*/

        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }

    @Override
    public void queryEgDitailOfIeRepairDetialOfIeReport(HttpServletRequest request, DataGrid dataGrid) {
        String year = request.getParameter("year");//年份
        String egId = request.getParameter("egId");//工程师ID

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	t.*, ");
        sql.append(" 	SUM(CASE WHEN avgLastEvaluate <= oth.avgOthers THEN 1 ELSE 0 END) AS rank ");
        sql.append(" FROM ");
        sql.append(" 	(SELECT ");
        sql.append(" 		eg.id AS egId, eg.`name` AS egName, dp.departname, ");
        sql.append(" 		FORMAT(AVG(ie.last_evaluate), 1) AS avgLastEvaluate, ");
        sql.append(" 		SUM(CASE WHEN last_evaluate <= 3 THEN 1 ELSE 0 END) AS blowThreeCount, ");
        sql.append(" 		ser.service_provider_code AS company ");
        sql.append(" 	FROM ");
        sql.append(" 		cbs_inspection_evaluate ie ");
        sql.append(" 	INNER JOIN cbs_service ser ON ser.id = ie.cbsservice_id ");
        sql.append(" 	LEFT JOIN zy_engineer eg ON eg.id = ser.engineer_id ");
        sql.append(" 	LEFT JOIN t_s_depart dp ON dp.id = eg.belong_org ");
        sql.append("	WHERE ");
        sql.append("		eg.id = '").append(egId).append("' ");
        /*---服务评价报表带来的条件---*/
        sql.append("		AND YEAR(ser.close_time) =").append(year);
        /*---服务评价报表带来的条件 end---*/
        sql.append(" )t ");
        sql.append(" LEFT JOIN ( ");
        sql.append(" 	SELECT ");
        sql.append(" 		FORMAT(AVG(ie.last_evaluate), 1) avgOthers, ser2.service_provider_code as company ");
        sql.append(" 	FROM ");
        sql.append(" 		cbs_inspection_evaluate ie ");
        sql.append(" 	INNER JOIN cbs_service ser2 ON ser2.id = ie.cbsservice_id ");
        sql.append(" 	GROUP BY ");
        sql.append(" 		ser2.engineer_id ");
        sql.append(" 	ORDER BY ");
        sql.append(" 		avgOthers ");
        sql.append(" ) oth ON oth.company = t.company ");

        System.out.println(sql);
        Map<String, String[]> paramsMap = request.getParameterMap();

        int totalCount = 0;
        Integer page = 1, rows = 10;
        rows = Integer.valueOf(null != paramsMap.get("rows") ? paramsMap.get("rows")[0] : "0");
        page = Integer.valueOf(null != paramsMap.get("page") ? paramsMap.get("page")[0] : "0");
        List<Map<String, Object>> rsMapList = new ArrayList<Map<String, Object>>();
        if (rows == 0 && page == 0) {
            rsMapList = findForJdbc(sql.toString());
        } else {
            dataGrid.setPage(page);
            dataGrid.setRows(rows);
            rsMapList = findForJdbc(sql.toString(), page, rows);
        }
        List<Map<String, Object>> countMapList = super.findForJdbc(sql.toString());
        if (null != countMapList && countMapList.size() > 0) {
            totalCount = countMapList.size();
        }
        dataGrid.setTotal(totalCount);
        dataGrid.setResults(rsMapList);
    }


    @Override
    public void queryIpRepairEvaluateReportDataOfEg(AjaxJson aj, String year, String ipCompany, String egIds) {
        String egIdStr = "";
        if (StringUtil.isNotEmpty(egIds)) {
            egIdStr = "'" + egIds.replace(",", "','") + "'";
        }
        List<List<Object>> dataList = new ArrayList<List<Object>>();
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	eg.id AS egId, ");
        sql.append(" 	eg.`NAME` AS egName, ");
        sql.append(" 	dp.org_code AS orgCode, ");
        sql.append(" 	dp.shortname ");
        sql.append(" FROM ");
        sql.append(" 	zy_engineer eg ");
        sql.append(" LEFT JOIN t_s_base_user bu ON bu.id = eg.ts_base_user_id ");
        sql.append(" LEFT JOIN t_s_user_org uo ON uo.user_id = bu.ID ");
        sql.append(" LEFT JOIN t_s_depart dp ON dp.id = uo.org_id ");
        sql.append(" WHERE ");
        sql.append(" 	dp.org_code = '").append(ipCompany).append("' ");
        sql.append(" AND dp.logic_delete = 0 ");
        if (StringUtil.isNotEmpty(egIdStr)) {
            sql.append(" AND eg.id IN(").append(egIdStr).append(") ");
        }
        List<Map<String, Object>> egMapList = super.findForJdbc(sql.toString());//查询所有工程师及所属机构

        System.out.println("sql1:"+sql);
        sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" 	ser.engineer_id AS engineer, ");
        sql.append(" 	IFNULL(TRUNCATE(AVG(ie.last_evaluate),2),0) AS avgScore, ");
        sql.append(" 	COUNT(ser.id) ipCount, ");
        sql.append(" 	COUNT(ie.id) ieCount, ");
        sql.append(" 	SUM(IF(last_evaluate = 5,1,0)) AS five, ");
        sql.append(" 	SUM(IF(last_evaluate = 4,1,0)) AS four, ");
        sql.append(" 	SUM(IF(last_evaluate = 3,1,0)) AS three, ");
        sql.append(" 	SUM(IF(last_evaluate = 2,1,0)) AS two, ");
        sql.append(" 	SUM(IF(last_evaluate = 1,1,0)) AS one ");
        sql.append(" FROM ");
        sql.append("  cbs_service ser  ");
        sql.append(" LEFT JOIN cbs_inspection_evaluate ie ON ser.id = ie.cbsservice_id ");
        sql.append(" WHERE ");
        sql.append(" 	YEAR (ser.close_time) = '").append(year).append("' ");
        sql.append("  and ser.engineer_id is not NULL ");
        sql.append("	AND ser.service_provider_code = '").append(ipCompany).append("' ");
        sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
        sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_2).append("' ");
        if (StringUtil.isNotEmpty(egIdStr)) {
            sql.append(" AND ser.engineer_id IN(").append(egIdStr).append(") ");
        }
        sql.append(" GROUP BY ser.engineer_id ");

        System.out.println("sql2:"+sql);
//        sql.append(" ORDER BY avgScore DESC,ipCount DESC ");
        List<Map<String, Object>> egGroupMapList = super.findForJdbc(sql.toString());
        for (Map<String, Object> egMap : egMapList) {
            List<Object> rowList = new ArrayList<Object>();
            rowList.add(egMap);
            boolean had = false;
            for (Map<String, Object> egGroupMap : egGroupMapList) {
                if (egGroupMap.get("engineer").toString().equals(egMap.get("egId").toString())) {
                    rowList.add(null != egGroupMap.get("avgScore") ? egGroupMap.get("avgScore").toString() : "0");
                    rowList.add(null != egGroupMap.get("ipCount") ? egGroupMap.get("ipCount").toString() : "0");
                    rowList.add(null != egGroupMap.get("ieCount") ? egGroupMap.get("ieCount").toString() : "0");
                    rowList.add(null != egGroupMap.get("five") ? egGroupMap.get("five").toString() : "0");
                    rowList.add(null != egGroupMap.get("four") ? egGroupMap.get("four").toString() : "0");
                    rowList.add(null != egGroupMap.get("three") ? egGroupMap.get("three").toString() : "0");
                    rowList.add(null != egGroupMap.get("two") ? egGroupMap.get("two").toString() : "0");
                    rowList.add(null != egGroupMap.get("one") ? egGroupMap.get("one").toString() : "0");
                    had = true;
                    break;
                }
            }
            if (!had) {
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
                rowList.add("0");
            }
            dataList.add(rowList);
        }
        Collections.sort(dataList, new Comparator<List<Object>>() {
            public int compare(List<Object> list1, List<Object> list2) {
                //按任务总数 由高到低排序（评价平均分排序放在js中），之所以没在sql加order 因为 结果外层还有个工程师列表循环
                return Double.valueOf(list2.get(2).toString()).compareTo(Double.valueOf(list1.get(2).toString()));
//        		if(Double.valueOf(list1.get(2).toString()) - Double.valueOf(list2.get(2).toString()) < 0){
//        			return 1;
//        		}
//        		return -1;
            }
        });
//		sql = new StringBuffer();
//		sql.append(" SELECT ");
//		sql.append(" 	IFNULL(TRUNCATE(AVG(ie.last_evaluate),2),0) AS avgScore ");
//		sql.append(" FROM ");
//		sql.append("  zy_inspection_plan ip  ");
//		sql.append(" LEFT JOIN cbs_inspection_evaluate ie ON ip.id = ie.inspection_id ");
//		sql.append(" WHERE ");
//		sql.append(" 	YEAR (ip.end_time) = '").append(year).append("' ");
//		sql.append("	AND ip.company = '").append(ipCompany).append("' ");
//		sql.append(" 	AND ie.evaluate_state = '").append(StaticValue.DIC_EVALUATE_STATE_1).append("' ");
//		sql.append(" 	AND ie.bus_type = '").append(StaticValue.DIC_EVALUATE_BUS_TYPE_1).append("' ");
//		Map<String,Object> cpAvgMap = super.findOneForJdbc(sql.toString());
//		Map<String, Object> rsMap = new HashMap<String, Object>();
//		if(StringUtil.isNotEmpty(cpAvgMap.get("avgScore"))){
//			rsMap.put("cpAvgScore", cpAvgMap.get("avgScore").toString());
//		}
//		aj.setAttributes(rsMap);
        aj.setObj(dataList);
    }

}